値・計算式の取得・設定
セルの値・数式を取得
セルの値や数式を調べて変数に代入したり、メッセージボックスに表示したりすることを、値又は数式の「取得」といいます。セルの値・数式の取得を行うときは、いちいち「Select」メソッドでセルを選択する必要はありません。
セルの値の取得には「Value｣プロパティを使用します。
セル A1 に 「10」という値が入力されているとき、
例1： 値 = Range("A1").Value
変数「値」には、セルA1に表示されている値
「10」 が代入されます。
セル A1 に 「=10+20」 という数式が入力されているとき、数式の結果の値を取得するには
例2： 値 = Range("A1").Value
変数「値」には、セルA1の数式の結果として表示されている値
「30」 が代入されます。
セルの数式の取得には「Formula」または「FormulaR1C1」プロパティを使用します。
セル A1 に 10、A2 に 20、A3 に = SUM(A1:A2)という数式(相対参照)が入力されているとき、
例3： 値 = Range("A3").Formula ・・・ A1形式
変数「値」には、「= SUM(A1:A2)」の式が代入されます。
例4： 値 = Range("A3").FormulaR1C1 ・・・ R1C1形式
変数「値」には、「= SUM(R[-2]C:R[-1]C)」の式が代入されます。
セル A1 に 10、A2 に 20、A3 に = SUM($A$1:$A$2)という数式(絶対参照)が入力されているとき、
例5： 値 = Range("A3").Formula ・・・ A1形式
変数「値」には、「= SUM($A$1:$A$2)」の式が代入されます。
例6： 値 = Range("A3").FormulaR1C1 ・・・ R1C1形式
変数「値」には、「= SUM(R1C1:R2C1)」の式が代入されます。
処理内容：アクティブセルの値を変数に代入します。
Sub value_1()
値 = ActiveCell.Value
End Sub
処理内容：セルA1の値をメッセージボックスに表示します。
Sub value_2()
MsgBox Range("A1").Value
End Sub
処理内容：セルA1の値を変数に代入します。(省略形)
Sub value_3()
値 = Range("A1")
End Sub
処理内容：セルA1の値を変数に代入します。(さらに省略形)
Sub value_4()
値 = [A1]
End Sub
処理内容：セルC1の数式をメッセージボックスに表示します。( A1形式 絶対参照)
Sub Formula_1()
Range("C1").Formula = "=$A$1+$B$1"
MsgBox Range("C1").Formula
End Sub
実行結果：「=$A$1+$B$1」が表示される。
処理内容：セルC1の数式をメッセージボックスに表示します。(
R1C1形式 絶対参照 )
Sub Formula_2()
Range("C1").Formula = "=$A$1+$B$1"
MsgBox Range("C1").FormulaR1C1
End Sub
実行結果：「=R1C1+R1C2」が表示される。
処理内容：セルC1の数式をメッセージボックスに表示します。( A1形式 相対参照)
Sub Formula_3()
Range("C1").Formula = "=A1+B1"
MsgBox Range("C1").Formula
End Sub
実行結果：「=A1+B1」が表示される。
処理内容：セルC1の数式をメッセージボックスに表示します。(
R1C1形式 相対参照 )
Sub Formula_4()
Range("C1").Formula = "=A1+B1"
MsgBox Range("C1").FormulaR1C1
End Sub
実行結果：「=RC[-2]+RC[-1」が表示される。
処理内容：セルC1の数式の結果をメッセージボックスに表示します。
Sub Formula_5()
Range("A1").Value = 10:Range("B1").Value = 20
Range("C1").Formula = "=A1+B1"
MsgBox Range("C1").Value
End Sub
実行結果：A1とB1の加算結果「30」が表示される。
セルに数値を設定(入力)
処理内容：1つのセルに数値を入力します。
Sub numeric_1()
Range("A1").Value = 1 ' 基本形
Range("A2") = 2 ' 省略形
[A3] = 3 ' さらに省略形
End Sub
処理内容：複数のセル(A1:A10)に一括して数値を入力します。
Sub numeric_2()
Range("A1:A10").Value = 5
End Sub
処理内容：変数に格納された数値を入力します。
Sub numeric_3()
Dim X As Long
X=10
Range("A1").Value = X
End Sub
処理内容：1つのセルに数値を入力します。
Sub numeric_4()
Cells(1,1).Value = 1 ' 基本形
Cells(1,2) = 2 ' 省略形
End Sub
処理内容：複数のセル(A1:A10)に一括して数値を入力します。
Sub numeric_5()
Range(Cells(1,1),Cells(10,1)).Value = 5
End Sub
処理内容：変数に格納された数値を入力します。
Sub numeric_6()
Dim X As Long
X=10
Cells(1,1).Value = X
End Sub
処理内容：小数点を含むセルを、小数点以下の桁数を指定して書式を設定します。
Sub numeric_7()
Dim K As Integer, zero As String
K = 2 '小数点以下の桁数
ActiveCell.Value = 1234.56789
zero = Application.WorksheetFunction.Rept("0", K)
Selection.NumberFormatLocal = "#,##0." & zero '
結果 = 1,234.57
End Sub
|
4 計算式が入力セルを、計算式の結果の数値に変換します。 |
処理内容：セルC1の計算式をその結果の数値に置き換えます。
Sub numeric_8()
Range("A1").Value = 10:Range("B1").Value= 20
Range("C1").Formula = "=A1+B1"
Range("C1").Value = Range("C1").Value
End Sub
セルC1が計算式ではなく、計算結果の数値になっていることを確認してください。
セルに文字列を設定(入力)
セルに文字列を設定するときは、入力文字をダブルクォーテーション(")で囲みます。
なお、数字を文字列として扱うときは、ダブルクォーテーションで囲ってもセルに入力されたときに「数値」と見なされますので、セルの書式設定で入力セルを文字列タイプに設定するか、入力文字の先頭に「
' (アポストロフィ)」を付けます。
処理内容：数字を文字列として入力します。
Sub text_1()
Dim X As String,Y As String
Range("A1").Value = "'001" ' 001 が入力される
X="002":Y="003"
Range("A2").Value = "'" & X ' 002 が入力される
Range("A3").Value = "'" & Format$(Y, "000") ' 003
が入力される
Range("A4").Value = "004" ' アポストロフィがないため数値の「4」になる
End Sub
処理内容：文字、日付、時刻を文字列として入力します。
Sub text_2()
Range("A1").Value = "VBA" '
文字の入力
Range("A2").Value = "2002/1/1"
'
西暦日付の入力
Range("A3").Value = CDate("H14/1/1") '
和暦日付の入力
Range("A4").Value = "16:15:40"
'
時間の入力(24時間表示)
Range("A5").Value = #4:15:40 PM# '
時間の入力(12時間表示)
Range("A6").Value = "4:15:40 AM"
'
時間の入力(12時間表示)
End Sub
セルに数式を設定(入力)
セルの数式の設定内容は、セルの参照方法によって異なります。A1形式の数式では「Formula」プロパティを、R1C1形式の入力では「FormulaR1C1」プロパティを使用します。
A1形式の数式入力は、セル番地をワークシートの列番号と行番号の組み合わせで表現するので馴染みが深く、解り易いのですが、R1C1形式の場合は少し予備知識が必要だと思います。
1. A1形式による数式
A1形式の数式は、ワークシートの数式と同じようにセル範囲を座標名で設定します。
処理内容：絶対参照で、セルE1からE4の合計をE5に入力します。(行間合計・・・縦計)
Sub Formula_1()
Range("E5").Formula = "=SUM($E$1:$E$4)"
End Sub
処理内容：相対参照で、セルE1からE4の合計をE5に入力します。(行間合計・・・縦計)
Sub Formula_2()
Range("E5").Formula = "=SUM(E1:E4)"
End Sub
2. R1C1形式の絶対参照による数式
R1C1形式の絶対参照の数式では、参照するセルの絶対的位置を行番号と列番号で表し、セルの参照を行います。「R」は行番号(Row)、「C」は列番号(Column)の意味で、セルの絶対的位置を「R」または「C」の右の数値で示します。
行番号を表す場合は、セルの1行目を「R1」、Rの右の数値はワークシートの行番号と一致します。
列番号を表す場合は、セルのA列を「C1」として、列が右方向に行くに従ってCの右の数値を1ずつ増やします。
処理内容：セルE1からE4の合計をE5に入力します。(行間合計・・・縦計)
Sub Formula_3()
Range("E5").FormulaR1C1 = "=SUM(R1C5:R4C5)"
End Sub
セル1行目の5列目から4行目の5列目までを合計します。
結果として、A1形式の「= SUM($E$1:$E$4)」と同じ意味になります。
処理内容：セルA5からD5の合計をE5に入力します。(列間合計・・・横計)
Sub Formula_4()
Range("E5").FormulaR1C1 = "=SUM(R5C1:R5C4)"
End Sub
セル5行目の1列目から5行目の4列目までを合計します。
結果として、A1形式の「= SUM($A$5:$D$5)」と同じ意味になります。
3. R1C1形式の相対参照による数式
R1C1形式の相対参照の数式では、数式が入力されているセルを基準とした相対的位置関係でセルの参照を行います。「R」は行(Row)、「C」は列(Column)の意味で、セルの相対的位置を「R」または「C」の右に[
] 内の数値で示します。
行の相対的位置は、基準セルからみて上方向は「−(マイナス)」数値で、下方向は「＋(プラス)」数値で表します。ただし、プラスの場合は記号を省略します。
列の相対的位置は、基準セルからみて左方向は「−(マイナス)」数値で、右方向は「＋(プラス)」数値で表します。ただし、プラスの場合は記号を省略します。
行または列が基準セルと同じである場合は、「R」または「C」の右の[ ]内数値を省略します。
処理内容：セルE1からE4の合計をE5に入力します。(行間合計・・・縦計)
Sub Formula_5()
Range("E5").FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
End Sub
セルE5の 4 行上の同じ列から、セルE5の 1行上の同じ列までを合計します。
結果として、A1形式の「= SUM(E1:E4)」と同じ意味になります。
処理内容：セルA5からD5の合計をE5に入力します。(列間合計・・・横計)
Sub Formula_6()
Range("E5").FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
End Sub
セルE5の同じ行の 4 列左から、セルE5の同じ行の1 列左までを合計します。
結果として、A1形式の「= SUM(A5:D5)」と同じ意味になります。
4. 他のセル・シート・ブックのセル参照
同一シートのセル参照 ・・・ セル番地を指定
他のシートのセル参照 ・・・ シート名の後ろに「 !
」マークを付ける
他のブックのセル参照 ・・・ ブック名を[ ]で囲む
|
4 ブック名「Book1」、シート名「Sheet1」から他のセルを参照 |
処理内容：同一シートのセルB3を参照します。
Sub Formula_7()
Range("A1").Formula = "=$B$3" ' A1形式絶対参照
Range("A2").Formula = "=B3"
' A1形式相対参照
Range("A3").Formula = "=R3C2" ' R1C1形式絶対参照
Range("A4").Formula = "=R[-1]C[1]" ' R1C1形式相対参照
End Sub
R1C1形式相対参照の場合、数式入力セルが基準セルとなります。セルA4からのセルB3相対的位置は1行上の1列右になります。従って、R[-1]、C[1]となるわけです。
処理内容：同じブックの「Sheet2」のセルB3を参照します。
Sub Formula_8()
Worksheets("Sheet1").Activate
Range("A5").Formula = "=Sheet2!B3" '
A1形式相対参照
Range("A6").Formula = "=Sheet2!R[-3]C[1]" '
R1C1形式相対参照
End Sub
異るシートを参照するときは、現在、どのシートがアクティブになっているか注意しましょう。
R1C1形式の相対的位置は、シートが異る場合でも計算式入力位置が基準セルとなります。
処理内容：別のブック「Book2」の「Sheet1」のセルB3を参照します。
Sub Formula_9()
Workbooks("Book1").Worksheets("Sheet1").Activate
Range("A7").Formula = "=[Book2]Sheet1!B3" '
A1形式相対参照
Range("A8").Formula = "=[Book2]Sheet1!R[-5]C[1]" '
R1C1形式相対参照
End Sub
異るブックを参照するときは、現在、どのブック・シートがアクティブになっているか注意しましょう。
R1C1形式の相対的位置は、ブックが異る場合でも計算式入力位置が基準セルとなります。
処理内容：基準セルと同じ位置にある「Book2」「Sheet1」のセル(A2:A7)を参照します。
Sub Formula_10()
Dim i As Long
Workbooks("Book1").Worksheets("Sheet1").Activate
For i = 2 To 7
Cells(i, 1).FormulaR1C1 = "=[Book2]Sheet1!RC"
Next i
End Sub
基準セルを、For〜Next ステートメントにより変化させ、R1C1形式の相対参照により参照します。
参照セル位置の「RC」は、行・列とも基準セルと同じセル位置を参照するという意味です。
5. 縦横計算例
処理内容：Offsetを使用した縦横計算。
Sub Formula_11()
Dim X As Long,Y As Long
Worksheets("Sheet1").Select
With Range("A11") ' 縦計(A1:A10)
Y=WorksheetFunction.Sum(Range(.Offset(-10), .Offset(-1)).Value)
End With
With Range("K1") ' 横計(A1:J1)
X=WorksheetFunction.Sum(Range(.Offset(,-10), .Offset(,-1)).Value)
End With
MsgBox "(A1:A10)縦計 = " & Y & vbCrLf & "(A1:J1)横計 = " & X
End Sub
処理内容：リスト範囲が不定な表の縦横一括計算。(A列及び1行目は見出し列・行)
Sub Formula_12()
Worksheets("Sheet1").Select
With Range("B2")
右端列 = .End(xlToRight).Column
最下行 = .End(xlDown).Row
End With
左上 = "B2"
左下 = Cells(最下行, 2).Address(False, False)
右上 = Cells(2, 右端列).Address(False, False)
' 横計
Range(Cells(2, 右端列 + 1), Cells(最下行, 右端列 + 1)).Formula = _
"=SUM(" & 左上 & ":" & 右上 & ")"
' 縦計
Range(Cells(最下行 + 1, 2), Cells(最下行 + 1, 右端列 + 1)).Formula = _
"=SUM(" & 左上 & ":" & 左下 & ")"
End Sub